package com.genghis.ptas.scorm.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.core.page.entity.Page;
import com.genghis.core.page.entity.PageParameter;
import com.genghis.ptas.scorm.dao.ScormDao;
import com.genghis.ptas.scorm.entity.*;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 字典dao实现
 *
 * @author tiangc
 * @version 1.0.1
 */

@Repository("scormDao")
public class ScormDaoImpl extends PageDao implements ScormDao {

    @Override
    public int addScorm(ScormInfo scormInfo) {
        String sql = "INSERT INTO scorm_info(scorm_name, upload_time, upload_user_id, course_type, course_disc, regist_num, ims_url) VALUES(:scormName, :uploadTime, :uploadUserId, :courseType, :courseDisc, :registNum, :imsUrl)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(scormInfo), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public int addSCOInfo(SCOInfo scoInfo) {
        String sql = "INSERT INTO sco_info(node_id, core_student_id, core_student_name, core_lesson_location, core_lesson_status," +
                " core_score, core_session_time, core_credit, core_entry, core_score_raw, core_total_time, core_lesson_mode, core_exit, suspend_data, launch_data) " +
                "VALUES(:nodeId, :coreStudentId, :coreStudentName, :coreLessonLocation, :coreLessonStatus, :coreScore, :coreSessionTime ,:coreCredit," +
                " :coreEntry, :coreScoreRaw, :coreTotalTime, :coreLessonMode, :coreExit, :suspendData, :launchData)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(scoInfo), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public void updateSCOInfoByNodeIdAndStuId(SCOInfo scoInfo) {
        String sql = "UPDATE sco_info SET core_student_name = :coreStudentName, core_lesson_location = :coreLessonLocation, core_lesson_status = :coreLessonStatus," +
                " core_score = :coreScore, core_session_time = :coreSessionTime, core_credit = :coreCredit, core_entry = :coreEntry, core_score_raw = :coreScoreRaw, core_total_time = :coreTotalTime, core_lesson_mode = :coreLessonMode, core_exit = :coreExit, " +
                "suspend_data = :suspendData, launch_data = :launchData WHERE node_id = :nodeId AND core_student_id= :coreStudentId ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(scoInfo));
    }

    @Override
    public int addNodesInfo(NodesInfo nodesInfo) {
        String sql = "INSERT INTO nodes_info(node_id, node_title, parent_id, scorm_id, node_type, url) VALUES(:nodeId, :nodeTitle, :parentId, :scormId, :nodeType, :url)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(nodesInfo), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public int delCourse(int id) {
        String sql = "DELETE FROM scorm_info WHERE id = ?";
        return getJdbcTemplate().update(sql, id);
    }

    @Override
    public void updateNodeInfoByScoId(NodesInfo nodesInfo) {
         String sql="UPDATE nodes_info SET sco_id= :scoId WHERE id= :nodeId";
         getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(nodesInfo));
    }

    @Override
    public SCOInfo getSCOInfoByNodeIdAndStuId(SCOInfo scoInfo) {
        String sql="SELECT * FROM sco_info WHERE node_id= ? AND core_student_id="+scoInfo.getCoreStudentId();
        try{
            return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<SCOInfo>(SCOInfo.class), scoInfo.getNodeId());
        }catch (Exception e){
            return null;
        }
    }


    @Override
    public Page<ScormInfo> findScormInfoPage(PageParameter pageParameter, ScormInfo scormInfo, int userId) {
        StringBuilder sql=new StringBuilder("SELECT s.id,s.scorm_name,s.upload_time,s.upload_user_id, u.login_name showUserName,s.course_type,s.course_disc FROM scorm_info s,user_info u WHERE s.upload_user_id=u.id AND s.upload_user_id ="+userId);
        if(!scormInfo.getScormName().equals("")&&scormInfo.getScormName()!=null){
           sql.append(" AND s.`scorm_name` LIKE '%").append(scormInfo.getScormName().trim()).append("%'");
        }
        if(scormInfo.getShowCourseType()!=null&&!scormInfo.getShowCourseType().equals("")){
            sql.append(" AND s.`course_type`=").append(scormInfo.getShowCourseType());
        }
        if(!("").equals(scormInfo.getUpDateBegin())&&scormInfo.getUpDateBegin()!=null){
            sql.append(" AND s.`upload_time` > '").append(scormInfo.getUpDateBegin()).append("'");
        }
        if(!("").equals(scormInfo.getUpDateEnd())&&scormInfo.getUpDateEnd()!=null){
            sql.append(" AND  s.`upload_time` <'").append(scormInfo.getUpDateEnd()).append("'");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(scormInfo), new BeanPropertyRowMapper<ScormInfo>(ScormInfo.class)) ;
    }
    @Override
    public Page<ScormInfo> findOnlinePageList(PageParameter pageParameter, ScormInfo scormInfo, int userId) {
        StringBuilder sql=new StringBuilder("SELECT s.*, u.`login_name` FROM scorm_info s, user_info u " +
                "WHERE s.`upload_user_id` = u.`id` AND s.`id` IN  (SELECT c.`scorm_id` "
                + "FROM  user_course_info c WHERE c.`user_id` = "+userId+") AND s.`upload_user_id` <>"+userId);
        if(!scormInfo.getScormName().equals("")&&scormInfo.getScormName()!=null){
            sql.append(" AND s.`scorm_name` LIKE '%").append(scormInfo.getScormName().trim
                    ()).append("%'");
        }
        if(scormInfo.getShowCourseType()!=null&&!scormInfo.getShowCourseType().equals("")){
            sql.append(" AND s.`course_type`=").append(scormInfo.getShowCourseType());
        }
        if(!("").equals(scormInfo.getUpDateBegin())&&scormInfo.getUpDateBegin()!=null){
            sql.append(" AND s.`upload_time` > '").append(scormInfo.getUpDateBegin()).append
                    ("'");
        }
        if(!("").equals(scormInfo.getUpDateEnd())&&scormInfo.getUpDateEnd()!=null){
            sql.append(" AND  s.`upload_time` <'").append(scormInfo.getUpDateEnd()).append
                    ("'");
        }
        return queryForPage(pageParameter, sql.toString(), new
                BeanPropertySqlParameterSource(scormInfo), new BeanPropertyRowMapper<ScormInfo>
                (ScormInfo.class)) ;
    }
    @Override
    public Page<ScormInfo> findCoursePageListByUserId(PageParameter pageParameter, ScormInfo scormInfo, int userId) {
        StringBuilder sql=new StringBuilder("SELECT s.*,u.`login_name` FROM scorm_info s,user_info u WHERE s.`upload_user_id`=u.`id` AND s.`id` IN(SELECT c.scorm_id FROM user_course_info c WHERE c.user_id="+userId+") ");
        if(!scormInfo.getScormName().equals("")&&scormInfo.getScormName()!=null){
            sql.append(" AND s.`scorm_name` LIKE '%").append(scormInfo.getScormName().trim()).append("%'");
        }
        if(scormInfo.getShowCourseType()!=null&&!scormInfo.getShowCourseType().equals("")){
            sql.append(" AND s.`course_type`=").append(scormInfo.getShowCourseType());
        }
        if(!("").equals(scormInfo.getUpDateBegin())&&scormInfo.getUpDateBegin()!=null){
            sql.append(" AND s.`upload_time` > '").append(scormInfo.getUpDateBegin()).append("'");
        }
        if(!("").equals(scormInfo.getUpDateEnd())&&scormInfo.getUpDateEnd()!=null){
            sql.append(" AND  s.`upload_time` <'").append(scormInfo.getUpDateEnd()).append("'");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(scormInfo), new BeanPropertyRowMapper<ScormInfo>(ScormInfo.class)) ;
    }

    @Override
    public void registScorm(ScormInfo scormInfo) {
        String sql = "UPDATE scorm_info SET regist_num=:registNum WHERE id=:id";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(scormInfo));
    }

    @Override
    public List<ScormInfo> findScormInfoPageList() {
        String sql="SELECT a.* ,b.id commentId,b.comment_score FROM scorm_info a LEFT JOIN scorm_comments b ON a.id=b.scorm_id";
        List<ScormInfo> scormInfoList= getJdbcTemplate().query(sql, new ScormInfoRomMapper());
        return scormInfoList;
    }

    @Override
    public ScormInfo findScormInfoById(int id) {
        String sql="SELECT * FROM scorm_info WHERE id= ?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<ScormInfo>(ScormInfo.class), id);
    }

    @Override
    public List<NodesInfo> listAllCourseByScormId(int scormId) {
        String sql = "SELECT *  FROM nodes_info WHERE scorm_id=" + scormId;
        return getJdbcTemplate().query(sql, new NodesInfoRomMapper());
    }

    @Override
    public int addUserCourseInfo(UserCourseInfo userCourseInfo) {
        String sql = "INSERT INTO user_course_info(user_id, scorm_id) VALUES(:userId, :scormId)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(userCourseInfo), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public boolean isRegistScorm(UserCourseInfo userCourseInfo) {
        String sql = "SELECT COUNT(u.id) FROM user_course_info u WHERE user_id="+userCourseInfo.getUserId()+" and scorm_id="+userCourseInfo.getScormId();
        int regNum= getJdbcTemplate().queryForInt(sql);
        boolean flag=false;
        if(regNum==0){
            flag=true;
        }
        return flag;
    }

    @Override
    public List<ScormInfo> listAllScormByUploadTime() {
        String sql="SELECT a.*,c.scorm_id,c.comment_score FROM scorm_info a LEFT JOIN (SELECT scorm_id,AVG(comment_score) comment_score FROM scorm_comments GROUP BY scorm_id) c \n" +
                " ON a.id=c.scorm_id ORDER BY UNIX_TIMESTAMP(a.upload_time) DESC ";
        List<ScormInfo> scormInfos=getJdbcTemplate().query(sql,new ScormInfoRomMapper());
        if(scormInfos.size()<6){
            return scormInfos;
        }else{
            sql= "SELECT a.*,c.scorm_id,c.comment_score FROM scorm_info a LEFT JOIN (SELECT scorm_id,AVG(comment_score) comment_score FROM scorm_comments GROUP BY scorm_id) c \n" +
                    " ON a.id=c.scorm_id ORDER BY UNIX_TIMESTAMP(a.upload_time) DESC LIMIT 0,6";
            return  getJdbcTemplate().query(sql,new ScormInfoRomMapper());
        }
    }

    @Override
    public List<ScormInfo> listAllScormByRegistNum() {
        String sql="SELECT a.*,c.scorm_id,c.comment_score FROM scorm_info a LEFT JOIN (SELECT scorm_id,AVG(comment_score) comment_score FROM scorm_comments GROUP BY scorm_id) c \n" +
                "ON a.id=c.scorm_id ORDER BY a.regist_num DESC";
        List<ScormInfo> scormInfos=getJdbcTemplate().query(sql,new ScormInfoRomMapper());
        if(scormInfos.size()<6){
            return scormInfos;
        }else{
            sql= "SELECT a.*,c.scorm_id,c.comment_score FROM scorm_info a LEFT JOIN (SELECT scorm_id,AVG(comment_score) comment_score FROM scorm_comments GROUP BY scorm_id) c \n" +
                    " ON a.id=c.scorm_id ORDER BY a.regist_num DESC LIMIT 0,6";
            return  getJdbcTemplate().query(sql,new ScormInfoRomMapper());
        }
    }

    @Override
    public List<ScormInfo> listAllScormByAvgScore() {
        String sql="SELECT a.*,c.scorm_id,c.comment_score FROM scorm_info a LEFT JOIN (SELECT scorm_id,AVG(comment_score) comment_score FROM scorm_comments GROUP BY scorm_id) c \n" +
                "ON a.id=c.scorm_id ORDER BY comment_score DESC";
        List<ScormInfo> scormInfos=getJdbcTemplate().query(sql,new ScormInfoRomMapper());
        if(scormInfos.size()<6){
            return scormInfos;
        }else{
            sql= "SELECT a.*,c.scorm_id,c.comment_score FROM scorm_info a LEFT JOIN (SELECT scorm_id,AVG(comment_score) comment_score FROM scorm_comments GROUP BY scorm_id) c \n" +
                    "ON a.id=c.scorm_id ORDER BY comment_score DESC LIMIT 0,6";
            return  getJdbcTemplate().query(sql,new ScormInfoRomMapper());
        }
    }

    @Override
    public List<ScormInfo> listAllScormByCourseType(int courseType) {
        String sql="SELECT *  FROM scorm_info WHERE course_type="+courseType;
        return getJdbcTemplate().query(sql,new BeanPropertyRowMapper<ScormInfo>(ScormInfo.class));
    }

    @Override
    public Page<ScormInfo> findCourseInfoPageList(PageParameter pageParameter, ScormInfo scormInfo, int userId) {
        StringBuilder sql = new StringBuilder("SELECT s.*,u.login_name showUserName FROM scorm_info s,user_info u WHERE s.upload_user_id=u.id");
        if (scormInfo.getScormName() != null && !scormInfo.getScormName().equals("")) {
            sql.append(" AND s.scorm_name like '%").append(scormInfo.getScormName().trim()).append("%'");
        }
        if (!("").equals(scormInfo.getShowCourseType())) {
            sql.append(" AND s.course_type=").append(scormInfo.getShowCourseType());
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(scormInfo), new BeanPropertyRowMapper<ScormInfo>(ScormInfo.class));
    }

    @Override
    public int delCourseInfo(int id) {
        String sql = "DELETE FROM scorm_info WHERE id = ?";
        return getJdbcTemplate().update(sql, id);
    }


}
